package JDBC;

import java.sql.*;

public class JDBC_Test {
    //防SQL注入
    public String loginUser(String name, String password) {
        String name1 = null;
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "SYSTEM", "root");
            String sql = "select * from usera where name=? and pwd=?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                name1 = rs.getString("name");
                System.out.println("登录成功！");
                break;
            }
        } catch (Exception e) {

        }
        return name1;
    }

    //事务
    public void sw() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "SYSTEM", "root");
            System.out.println(1);
            con.setAutoCommit(false);
            String sql1 = "update bank set ty_money=ty_money+200 where card=1001";
            String sql2 = "update bank set ty_money=ty_money-200 where card=1002";
            PreparedStatement ps = con.prepareStatement(sql1);
            PreparedStatement ps2 = con.prepareStatement(sql2);
            System.out.println(2);
            int a = ps.executeUpdate();
            int b = ps2.executeUpdate();
            System.out.println(3);
            System.out.println("a=" + a);
            System.out.println("b=" + b);
            //这里不写关闭的话，可能无法提交数据
            con.close();
            ps.close();
            ps2.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //3.调用存储过程
    public void pro(int input) {
        int output = 0;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "SYSTEM", "root");
            //1.存储过程的调用,必须在数据库中建立了才行
            CallableStatement callablestatement = conn.prepareCall("{call select_proc(?,?)}");//双引号+花括号调用存储过程,如果有参数，就使用？，其中的？用来传递参数进去
            //2.因为存储过程有两个参数一个是输入，一个是输出,以下是对参数的设置
            callablestatement.setInt(1, input);
            callablestatement.registerOutParameter(2, 1);//registerOutParameter(int parameterIndex,int sqlType)，按顺序位置parameterIndex 将OUT参数注册为JDBC类型sqlType，sqlType为Types类中的常量
            //3.执行存储过程，执行后会将结果存到callablestatement对象里面
            callablestatement.execute();//
            //4.打印存储过程，康康
            System.out.println(callablestatement.getInt(2));//输出第二个参数，这里指的是上方的registerOutParameter里面的

        } catch (Exception e) {

        }
    }

}
